import sqlite3
import csv
import os
from flask import Flask, render_template, jsonify, request

app = Flask(__name__, template_folder='templates', static_folder='static')

# 数据库初始化
def init_db():
    conn = sqlite3.connect('data.db')
    c = conn.cursor()
    
    # 创建单词表
    c.execute('''CREATE TABLE IF NOT EXISTS words (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    level INTEGER NOT NULL,
                    unit INTEGER NOT NULL,
                    word TEXT NOT NULL
                )''')
    
    # 创建答题记录表
    c.execute('''CREATE TABLE IF NOT EXISTS quiz_records (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT NOT NULL,
                    quiz_type TEXT NOT NULL,
                    word_id INTEGER NOT NULL,
                    is_correct BOOLEAN NOT NULL,
                    time_used REAL NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )''')
    
    # 创建用户统计表
    c.execute('''CREATE TABLE IF NOT EXISTS user_stats (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT UNIQUE NOT NULL,
                    total_quizzes INTEGER DEFAULT 0,
                    correct_answers INTEGER DEFAULT 0,
                    total_time REAL DEFAULT 0.0,
                    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )''')
    
    conn.commit()
    conn.close()

# 从CSV文件导入单词数据
def import_words_from_csv():
    conn = sqlite3.connect('data.db')
    c = conn.cursor()
    
    # 检查是否已经导入过数据
    c.execute("SELECT COUNT(*) FROM words")
    count = c.fetchone()[0]
    
    if count == 0:  # 如果没有数据才导入
        with open('static/word.csv', 'r', encoding='utf-8') as f:
            reader = csv.reader(f)
            next(reader)  # 跳过标题行
            for row in reader:
                c.execute("INSERT INTO words (level, unit, word) VALUES (?, ?, ?)", 
                         (int(row[0]), int(row[1]), row[2]))
        
        conn.commit()
    
    conn.close()

# 首页路由
@app.route('/')
def index():
    return render_template('index.html')

# 设置页面路由
@app.route('/settings')
def settings():
    return render_template('settings.html')

# 考试页面路由
@app.route('/quiz')
def quiz():
    return render_template('quiz.html')

# 结果页面路由
@app.route('/results')
def results():
    return render_template('results.html')

# 答题记录页面路由
@app.route('/records')
def records():
    return render_template('records.html')

# 错题本页面路由
@app.route('/wrong_questions')
def wrong_questions():
    return render_template('wrong_questions.html')

# 排行榜页面路由
@app.route('/leaderboard')
def leaderboard():
    return render_template('leaderboard.html')

# API: 获取单词数据
@app.route('/api/words')
def get_words():
    try:
        conn = sqlite3.connect('data.db')
        c = conn.cursor()
        
        # 获取查询参数
        levels = request.args.getlist('levels', type=int)
        units = request.args.getlist('units', type=int)
        
        # 构造查询语句
        query = "SELECT id, level, unit, word FROM words WHERE 1=1"
        params = []
        
        if levels:
            placeholders = ','.join('?' * len(levels))
            query += f" AND level IN ({placeholders})"
            params.extend(levels)
        
        if units:
            placeholders = ','.join('?' * len(units))
            query += f" AND unit IN ({placeholders})"
            params.extend(units)
        
        query += " ORDER BY level, unit"
        
        c.execute(query, params)
        words = [{'id': row[0], 'level': row[1], 'unit': row[2], 'word': row[3]} for row in c.fetchall()]
        
        conn.close()
        return jsonify(words)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# API: 保存考试记录
@app.route('/api/quiz_records', methods=['POST'])
def save_quiz_record():
    try:
        data = request.get_json()
        
        # 验证必要字段
        required_fields = ['user_id', 'quiz_type', 'word_id', 'is_correct', 'time_used']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'Missing required field: {field}'}), 400
        
        conn = sqlite3.connect('data.db')
        c = conn.cursor()
        
        c.execute("""INSERT INTO quiz_records 
                     (user_id, quiz_type, word_id, is_correct, time_used) 
                     VALUES (?, ?, ?, ?, ?)""",
                  (data['user_id'], data['quiz_type'], data['word_id'], 
                   data['is_correct'], data['time_used']))
        
        # 更新用户统计数据
        c.execute("""INSERT OR IGNORE INTO user_stats 
                     (user_id, total_quizzes, correct_answers, total_time) 
                     VALUES (?, 0, 0, 0)""", (data['user_id'],))
        
        c.execute("""UPDATE user_stats 
                     SET total_quizzes = total_quizzes + 1,
                         correct_answers = correct_answers + ?,
                         total_time = total_time + ?,
                         last_updated = CURRENT_TIMESTAMP
                     WHERE user_id = ?""",
                  (1 if data['is_correct'] else 0, data['time_used'], data['user_id']))
        
        conn.commit()
        conn.close()
        
        return jsonify({'status': 'success'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# API: 获取排行榜
@app.route('/api/leaderboard')
def get_leaderboard():
    try:
        conn = sqlite3.connect('data.db')
        c = conn.cursor()
        
        c.execute("""SELECT user_id, total_quizzes, correct_answers, total_time,
                     CASE WHEN total_quizzes > 0 THEN CAST(correct_answers AS FLOAT) / total_quizzes * 100 ELSE 0 END as accuracy
                     FROM user_stats 
                     ORDER BY accuracy DESC, total_quizzes DESC
                     LIMIT 10""")
        
        leaderboard = [{
            'user_id': row[0],
            'total_quizzes': row[1],
            'correct_answers': row[2],
            'total_time': row[3],
            'accuracy': round(row[4], 2)
        } for row in c.fetchall()]
        
        conn.close()
        return jsonify(leaderboard)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# API: 获取答题记录
@app.route('/api/quiz_records')
def get_quiz_records():
    try:
        conn = sqlite3.connect('data.db')
        c = conn.cursor()
        
        c.execute("""SELECT qr.created_at, qr.quiz_type, w.word, qr.is_correct, qr.time_used
                     FROM quiz_records qr
                     JOIN words w ON qr.word_id = w.id
                     ORDER BY qr.created_at DESC
                     LIMIT 50""")
        
        records = [{
            'date': row[0],
            'type': row[1],
            'word': row[2],
            'is_correct': row[3],
            'time_used': row[4]
        } for row in c.fetchall()]
        
        conn.close()
        return jsonify(records)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# API: 获取错题
@app.route('/api/wrong_questions')
def get_wrong_questions():
    try:
        conn = sqlite3.connect('data.db')
        c = conn.cursor()
        
        c.execute("""SELECT w.word, qr.quiz_type, MAX(qr.created_at) as last_review, COUNT(*) as review_count
                     FROM quiz_records qr
                     JOIN words w ON qr.word_id = w.id
                     WHERE qr.is_correct = 0
                     GROUP BY w.word, qr.quiz_type
                     ORDER BY last_review DESC""")
        
        wrong_questions = [{
            'word': row[0],
            'type': row[1],
            'last_review': row[2],
            'review_count': row[3]
        } for row in c.fetchall()]
        
        conn.close()
        return jsonify(wrong_questions)
    except Exception as e:
        return jsonify({'error': str(e)}), 500

if __name__ == '__main__':
    init_db()
    import_words_from_csv()
    app.run(debug=True, host='0.0.0.0', port=5555)